package br.com.ffm.praticas.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.LinkedList;
import java.util.List;

import br.com.ffm.praticas.bean.LegalCostumer;
import br.com.ffm.praticas.bean.PersonType;
import br.com.ffm.praticas.connection.ConnectionDB;
import br.com.ffm.praticas.constants.Constant;

public class LegalCostumerDAO {
	
	/**
	 * Method that returns all the Legal Costumers
	 * @return
	 * @throws SQLException
	 */
	@SuppressWarnings("deprecation")
	public List<LegalCostumer> getLegalCostumers() throws SQLException {
		String query = "SELECT * FROM person p INNER JOIN person_type pt ON ((p.person_type_id = pt.id) and (pt.description = 'Legal Costumer'))";
		AddressDAO addressDAO = new AddressDAO();
		ContactDAO contactDAO = new ContactDAO();
		PersonTypeDAO personTypeDAO = new PersonTypeDAO();
		List<LegalCostumer> list = new LinkedList<LegalCostumer>();
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		while (result.next()) {
			LegalCostumer legalCostumer = new LegalCostumer();
			legalCostumer.setId(result.getInt("id"));
			legalCostumer.setContact(contactDAO.getContact(result.getInt("contact_id")));
			legalCostumer.setHomeAdress(addressDAO.getAddress(result.getInt("home_address_id")));
			if (result.getInt("business_address_id") != 0)
				legalCostumer.setBusinessAdress(addressDAO.getAddress(result.getInt("business_address_id")));
			legalCostumer.setPersonType(personTypeDAO.getPersonType(result.getInt("person_type_id")));
			if (result.getString("name") != null)
				legalCostumer.setName(result.getString("name"));
			if (result.getString("last_name") != null)
				legalCostumer.setLastName(result.getString("last_name"));
			if (result.getString("rg") != null)
				legalCostumer.setRg(result.getString("rg"));
			if (result.getString("cpf") != null)
				legalCostumer.setCpf(result.getString("cpf"));
			if (result.getString("cnpj") != null)
				legalCostumer.setCnpj(result.getString("cnpj"));
			legalCostumer.setTotalBuy(result.getInt("total_buy"));
			if (result.getDate("birthday") != null) {
				int day = result.getDate("birthday").getDay();
				int month = result.getDate("birthday").getMonth();
				int year = result.getDate("birthday").getYear();
				GregorianCalendar birthday = new GregorianCalendar(year, month, day);
				legalCostumer.setBirthday(birthday);
			}
			list.add(legalCostumer);
		}
		ConnectionDB.getInstance().close();
		return list;
	}
	
	/**
	 * Method that returns a specific Legal Costumer for your id
	 * @param id Single Costumer identifier
	 * @return LegalCostumer
	 * @throws SQLException
	 */
	@SuppressWarnings("deprecation")
	public LegalCostumer getLegalCostumer(int id) throws SQLException {
		String query = "SELECT * FROM person p INNER JOIN person_type pt ON ((p.person_type_id = pt.id) and (pt.description = 'Legal Costumer')) WHERE (p.id = " + id + " )";
		AddressDAO addressDAO = new AddressDAO();
		ContactDAO contactDAO = new ContactDAO();
		PersonTypeDAO personTypeDAO = new PersonTypeDAO();
		LegalCostumer legalCostumer;
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		if (result.next()) {
			legalCostumer = new LegalCostumer();
			legalCostumer.setId(result.getInt("id"));
			legalCostumer.setContact(contactDAO.getContact(result.getInt("contact_id")));
			legalCostumer.setHomeAdress(addressDAO.getAddress(result.getInt("home_address_id")));
			if (result.getInt("business_address_id") != 0)
				legalCostumer.setBusinessAdress(addressDAO.getAddress(result.getInt("business_address_id")));
			legalCostumer.setPersonType(personTypeDAO.getPersonType(result.getInt("person_type_id")));
			if (result.getString("name") != null)
				legalCostumer.setName(result.getString("name"));
			if (result.getString("last_name") != null)
				legalCostumer.setLastName(result.getString("last_name"));
			if (result.getString("rg") != null)
				legalCostumer.setRg(result.getString("rg"));
			if (result.getString("cpf") != null)
				legalCostumer.setCpf(result.getString("cpf"));
			if (result.getString("cnpj") != null)
				legalCostumer.setCnpj(result.getString("cnpj"));
			legalCostumer.setTotalBuy(result.getInt("total_buy"));
			if (result.getDate("birthday") != null) {
				int day = result.getDate("birthday").getDay();
				int month = result.getDate("birthday").getMonth();
				int year = result.getDate("birthday").getYear();
				GregorianCalendar birthday = new GregorianCalendar(year, month, day);
				legalCostumer.setBirthday(birthday);
			}
			ConnectionDB.getInstance().close();
			return legalCostumer;
		}
		ConnectionDB.getInstance().close();
		return null;
	}
	
	/**
	 * Method that returns a specific Legal Costumer for your CNPJ
	 * @param cnpj Single Costumer CNPJ
	 * @return LegalCostumer
	 * @throws SQLException
	 */
	@SuppressWarnings("deprecation")
	public LegalCostumer getLegalCostumer(String cnpj) throws SQLException {
		String query = "SELECT * FROM person p INNER JOIN person_type pt ON ((p.person_type_id = pt.id) and (pt.description = 'Legal Costumer')) WHERE (p.cnpj = '" + cnpj + "' )";
		AddressDAO addressDAO = new AddressDAO();
		ContactDAO contactDAO = new ContactDAO();
		PersonTypeDAO personTypeDAO = new PersonTypeDAO();
		LegalCostumer legalCostumer;
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		if (result.next()) {
			legalCostumer = new LegalCostumer();
			legalCostumer.setId(result.getInt("id"));
			legalCostumer.setContact(contactDAO.getContact(result.getInt("contact_id")));
			legalCostumer.setHomeAdress(addressDAO.getAddress(result.getInt("home_address_id")));
			if (result.getInt("business_address_id") != 0)
				legalCostumer.setBusinessAdress(addressDAO.getAddress(result.getInt("business_address_id")));
			legalCostumer.setPersonType(personTypeDAO.getPersonType(result.getInt("person_type_id")));
			if (result.getString("name") != null)
				legalCostumer.setName(result.getString("name"));
			if (result.getString("last_name") != null)
				legalCostumer.setLastName(result.getString("last_name"));
			if (result.getString("rg") != null)
				legalCostumer.setRg(result.getString("rg"));
			if (result.getString("cpf") != null)
				legalCostumer.setCpf(result.getString("cpf"));
			if (result.getString("cnpj") != null)
				legalCostumer.setCnpj(result.getString("cnpj"));
			legalCostumer.setTotalBuy(result.getInt("total_buy"));
			if (result.getDate("birthday") != null) {
				int day = result.getDate("birthday").getDay();
				int month = result.getDate("birthday").getMonth();
				int year = result.getDate("birthday").getYear();
				GregorianCalendar birthday = new GregorianCalendar(year, month, day);
				legalCostumer.setBirthday(birthday);
			}
			ConnectionDB.getInstance().close();
			return legalCostumer;
		}
		ConnectionDB.getInstance().close();
		return null;
	}
	
	/**
	 * Method that insert a Legal Costumer
	 * @param legalCostumer Legal Costumer that will be inserted
	 * @return Legal Costumer id
	 * @throws SQLException
	 */
	public int insert(LegalCostumer legalCostumer) throws SQLException {
		/* INSERT CONTACT */
		ContactDAO contactDAO = new ContactDAO();
		int contactId = contactDAO.insert(legalCostumer.getContact());
		
		/* INSERT ADDRES */
		AddressDAO addressDAO = new AddressDAO();
		int homeAddressId = addressDAO.insert(legalCostumer.getHomeAdress());
		int businessAddressId = addressDAO.insert(legalCostumer.getBusinessAdress());
		
		/* INSERT LEGAL COSTUMER */
		PersonTypeDAO personTypeDAO = new PersonTypeDAO();
		PersonType personType = personTypeDAO.getPersonType(legalCostumer.getPersonType().getDescription());
		
		String query = "INSERT INTO person (contact_id, home_address_id, business_address_id, person_type_id, name, last_name, rg, cpf, cnpj, birthday) ";
		query += "values (";
		query += contactId + ", ";
		query += homeAddressId + ", ";
		query += businessAddressId + ", ";
		query += personType.getId() + ", ";
		query += "'" + legalCostumer.getName() + "', ";
		query += "'" + legalCostumer.getLastName() + "', ";
		query += "'" + legalCostumer.getRg() + "', ";
		query += "'" + legalCostumer.getCpf() + "', ";
		query += "'" + legalCostumer.getCnpj() + "', ";
		query += "'" + getDate(legalCostumer.getBirthday()) + "')";
		ConnectionDB.getInstance().executeCommand(query);
		
		query = "SELECT MAX(id) FROM person";
		int legalCostumerId = ConnectionDB.getInstance().executeScalar(query, Constant.SCALAR_COMMAND_MAX, "id");
		return legalCostumerId;
	}
	
	/**
	 * Method that delete a Legal Costumer
	 * @param legalCostumer Legal Costumer that will be deleted
	 * @return true it will have been deleted or false if it will not be deleted
	 */
	public boolean delete(LegalCostumer legalCostumer) {
		String query = "DELETE FROM person WHERE (id = " + legalCostumer.getId() + " )";
		try {
			ConnectionDB.getInstance().executeCommand(query);
			return true;
		} catch (SQLException sqlExcp) {
			sqlExcp.printStackTrace();
			return false;
		}
	}
	
	/**
	 * Method that update a Legal Costumer
	 * @param legalCostumer Legal Costumer that will be updated
	 * @return true it will have been updated or false if it will not be updated
	 */
	public boolean update(LegalCostumer legalCostumer) {
		/* UPDATE CONTACT */
		ContactDAO contactDAO = new ContactDAO();
		contactDAO.update(legalCostumer.getContact());
		
		/* UPDATE ADDRESSES */
		AddressDAO addressDAO = new AddressDAO();
		addressDAO.update(legalCostumer.getHomeAdress());
		addressDAO.update(legalCostumer.getBusinessAdress());
		
		/* UPDATE LEGAL COSTUMER */
		String query = "UPDATE person SET ";
		query += "contact_id = " + legalCostumer.getContact().getId() + ", ";
		query += "name = '" + legalCostumer.getName() + "', ";
		query += "last_name = '" + legalCostumer.getLastName() + "', ";
		query += "rg = '" + legalCostumer.getRg() + "', ";
		query += "cpf = '" + legalCostumer.getCpf() + "', ";
		query += "cnpj = " + legalCostumer.getCnpj() + ", ";
		//QUANTIDADE DE ITENS VENDIDOS COMPRADOS??
		query += "birthday = '" + getDate(legalCostumer.getBirthday()) + "' ";
		query += "WHERE (id = " + legalCostumer.getId() + " )";
		try {
			ConnectionDB.getInstance().executeCommand(query);
			return true;
		} catch (SQLException sqlExcp) {
			sqlExcp.printStackTrace();
			return false;
		}
	}
	
	/**
	 * Method that returns a Formated Date
	 * Egg: YYYY/MM/DD -> '2009/01/13'
	 * @param date GregorianCalendar that will be formated
	 * @return Formated Date String
	 */
	private String getDate(GregorianCalendar date) {
		String formatedDate = date.get(Calendar.YEAR) + "/" + (date.get(Calendar.MONTH) + 1) + "/" + date.get(Calendar.DAY_OF_MONTH);
		return formatedDate;
	}

}
